Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Server OS Tuning

You may have to tune the server OS to provide for optimal I/O performance. Some of the things you may have to tune in the server OS are listed here; remember that some OSes may not require any tuning in these areas:

  Memory. Tune the system to reduce unnecessary memory usage so that Oracle can use as much of the system’s memory as possible for the SGA and server processes.
  Memory enhancements. Take advantage of 4M pages and ISM, if they are available. Both features can improve Oracle performance in a BLOB environment.
  I/O. Tune I/O to allow for optimal performance and use of AIO.
  Scheduler. If possible, turn off preemptive scheduling and load balancing. In a BLOB system, allowing a process to run to completion (that is, so that it is not preempted) is beneficial.
  Cache affinity. You may see some benefits from cache affinity because the amount of data is large and the number of processes is few.

The server operating system is mainly a host on which Oracle does its job. Any work done by the operating system is essentially overhead for Oracle. By optimizing code paths and reducing OS overhead, you can enhance Oracle performance.

Enhancements

You may want to make some additional enhancements to improve the performance of your system. Listed here are some of the items that can offer performance improvement; also listed are a few items that do not improve performance. Each item is accompanied by an explanation.

  Block size. In a BLOB system, increasing the database block size greatly improves performance. Because many of the queries access the database in a sequential manner, even though concurrent queries randomize the I/O, having a larger block size brings more of the rows into the SGA at once. Having these additional rows in the SGA can benefit you because you will be using them.
  Clusters and hash clusters. I don’t believe that clusters or hash clusters are of any benefit in this type of system.
  Indexes. Because the size of the data is very large, the database is full of data that represents a relatively small number of rows. Using an index may be beneficial in this case because a table scan involves lots of unused data.
  Parallel Query option. The Oracle Parallel Query option is of no use to you in this type of system.

Some of these enhancements can help your performance. The specific effect on your system will vary, but the information given here is true for most general cases.

Hardware Enhancements

In a BLOB system, several hardware enhancements can help you improve performance. These hardware enhancements can be beneficial in the area of CPU, I/O, and network, as described in the following sections.

CPU Enhancements

Enhancing the CPUs on your SMP or MPP system can provide instantaneous performance improvements—assuming that your application is causing the system to become CPU bound.

For SMP or MPP machines, the process of enhancing the CPU may be as simple as adding an additional CPU board. Before you purchase an additional processor of the same type and speed, consider upgrading to a faster processor. In this type of environment, both the addition of faster processors and more processors benefit the overall performance of the system.

I/O Enhancements

You can enhance I/O by adding disk drives or purchasing a hardware disk array. Systems that access BLOB data can benefit from the disk striping available in both hardware and software disk arrays. Using Oracle data file striping can also help the performance of this type of system.

Hardware and software disk arrays have the added benefit of optional fault tolerance. As described in Chapter 15, “Disk Arrays,” each of the fault-tolerant RAID levels has its advantages and disadvantages. You should first choose the correct fault tolerance for your needs and then make sure that you have sufficient I/O capabilities to achieve the required performance level. If you use fault tolerance, you will most likely have to increase the number of disk drives in your system.


TIP:  Because BLOB systems involve a majority of read operations and very few writes, you can take advantage of the fault tolerance and read performance of RAID-5. You should make sure that write activity is small.

Another benefit of hardware disk arrays is caching. Most disk arrays on the market today offer some type of write or read/write cache on the controller. The effect of this cache is to improve the speed of writing to the disk; the cache also masks the overhead associated with fault tolerance. If your queries often perform table scans, you may see good improved performance with disk controllers that take advantage of read-ahead features.

Read-ahead occurs when the controller detects a sequential access and reads an entire track (or some other large amount of data) and caches the additional data in anticipation of a request from the OS. Unlike an OLTP system in which this is just wasted overhead, in the BLOB system, you will need that data soon; if it is in the cache, it will be available very quickly.


CAUTION:  It is important to make sure that any controller you use with a write cache is protected against a power failure. Some disk array controllers on the market today (such as the Compaq disk array controllers) offer a battery-backed, mirrored memory cache that protects your data. Remember: Once Oracle believes that data has been written to the drive, that data had better be there.

Enhancements to the I/O subsystem make all the difference in the world to a BLOB environment because large amounts of data are being continually read. Be sure that you have a sufficient number of disk drives, properly configured. An I/O bottleneck is usually difficult to work around. As with all types of systems, a well-tuned application is very important.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.